package com.ls.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 导出工具类
 * </p>
 *
 * @author TangQuanBin
 * @since 2019/9/26 20:07
 */
public class ExportExcelUtil {

    /**
     * 导出EXCEL
     *
     * @param excelName    Excel名字   xxx.xls
     * @param headers      表头
     * @param exportFields 传入结果集中datas要导出的数据字段名
     * @param exportDatas  导出数据列表集合
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    public static void exportExcel(String excelName, String[] headers,
                                   String[] exportFields, List<?> exportDatas, HttpServletResponse response) throws Exception {
        OutputStream output = response.getOutputStream();
        try {
            // 创建HSSFWorkbook对象
            XSSFWorkbook wb = new XSSFWorkbook();
            // 创建HSSFSheet对象
            XSSFSheet sheet = wb.createSheet("sheet0");
            // 设置表格默认列宽度为20个字节
            sheet.setDefaultColumnWidth((short) 20);
            // 生成一个样式
            XSSFCellStyle style = wb.createCellStyle();
            //设置样式
            setStyle(style);

            // 创建HSSFRow对象(第一行，表头)
            XSSFRow row = sheet.createRow(0);
            //循环写表头
            for (short i = 0; i < headers.length; i++) {
                // 把字体应用到当前的样式
                style.setFont(getBoldFont(wb));
                // 创建HSSFCell对象
                XSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                cell.setCellValue(headers[i]);
            }


            //循环设值
            if (exportDatas != null) {
                for (int j = 0; j < exportDatas.size(); j++) {
                    //从第二行开始写
                    row = sheet.createRow(j + 1);
                    //导出偏移量，用于过滤数据
                    for (int k = 0; k < exportFields.length; k++) {
                        String fieldName = exportFields[k];
                        XSSFCell cell = row.createCell(k);
                        cell.setCellStyle(style);
                        //反射获取get方法和值
                        String getMethodName = "get"
                                + fieldName.substring(0, 1).toUpperCase()
                                + fieldName.substring(1);
                        Method getMethod = exportDatas.get(j).getClass().getMethod(getMethodName, new Class[]{});
                        Object value = getMethod.invoke(exportDatas.get(j), new Object[]{});

                        // 把字体应用到当前的样式
                        style.setFont(getBlackFont(wb));
                        cell.setCellStyle(style);
                        //类型转化
                        convertValue(cell, value);

                        //图片处理
                        if (value instanceof byte[]) {
                            sheet.addMergedRegion(new CellRangeAddress(j + 1, j + 1, j + 1, j + 1));
                            XSSFDrawing xssfDrawing = sheet.createDrawingPatriarch();
                            XSSFClientAnchor anchor = new XSSFClientAnchor(480, 30, 700, 250, (short) k, j + 1, (short) k + 1, j + 2);
                            xssfDrawing.createPicture(anchor, wb.addPicture((byte[]) value, XSSFWorkbook.PICTURE_TYPE_JPEG));
                            sheet.setColumnWidth((short) 800, (short) 800);
                            row.setHeight((short) 800);
                        }
                    }
                }
            }
            // 导出Excel文件
            response.reset();
            // 赋予名称
            String fileName = URLEncoder.encode(excelName, "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ";" + "filename*=utf-8''" + fileName);
            wb.write(output);
        } finally {
            output.close();
        }
    }


    /**
     * 生成Excel导入模板
     *
     * @param excelName Excel名字 xxx.xls
     * @param headers   Excel列标题(数组)
     * @param downData  下拉框数据(数组)
     * @param downRows  下拉列的序号(数组,序号从0开始)
     */
    public static void createExcelTemplate(String excelName, String[] headers,
                                           List<String[]> downData, int[] downRows, HttpServletResponse response) throws IOException {

        OutputStream output = response.getOutputStream();
        // 创建HSSFWorkbook对象
        try {
            XSSFWorkbook wb = new XSSFWorkbook();

            // 生成一个样式
            XSSFCellStyle style = wb.createCellStyle();

            //设置样式
            setStyle(style);
            //新建sheet
            XSSFSheet sheet1 = wb.createSheet("Sheet1");
            // 设置表格默认列宽度为20个字节
            sheet1.setDefaultColumnWidth((short) 20);

            //生成sheet1内容，第一个sheet的第一行为标题
            XSSFRow rowFirst = sheet1.createRow(0);
            //写标题
            for (int i = 0; i < headers.length; i++) {
                // 把字体应用到当前的样式
                style.setFont(getBoldFont(wb));
                // 创建HSSFCell对象
                XSSFCell cell = rowFirst.createCell(i);
                cell.setCellStyle(style);
                cell.setCellValue(headers[i]);
            }

            //设置下拉框数据
            for (int r = 0; r < downRows.length; r++) {
                //获取下拉对象
                String[] dlData = downData.get(r);
                int rownum = downRows[r];
                DataValidation dataValidation = setDataValidation(sheet1, dlData, 1, 10000, rownum, rownum);
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.createPromptBox("输入提示", "请选择下拉列中的内容！");
                dataValidation.setShowPromptBox(true);
                sheet1.addValidationData(dataValidation);
            }

            // 导出Excel文件
            response.reset();
            // 赋予名称
            String fileName = URLEncoder.encode(excelName, "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ";" + "filename*=utf-8''" + fileName);
            wb.write(output);
        } finally {
            output.close();
        }
    }


    /**
     * 下拉列表元素设置(255以内的下拉)
     *
     * @param @param  sheet
     * @param @param  textList
     * @param @param  firstRow
     * @param @param  endRow
     * @param @param  firstCol
     * @param @param  endCol
     * @param @return
     * @return DataValidation
     */
    private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int lastRow, int firstCol, int lastCol) {

        if (firstRow < 0 || lastRow < 0 || firstCol < 0 || lastCol < 0 || lastRow < firstRow || lastCol < firstCol) {
            throw new IllegalArgumentException("Wrong Row or Column index : " + firstRow + ":" + lastRow + ":" + firstCol + ":" + lastCol);
        }
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        //DVConstraint constraint = new DVConstraint();
        constraint.setExplicitListValues(textList);

        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);

        //数据有效性对象
        return helper.createValidation(constraint, regions);
    }


    /**
     * 类型转化
     *
     * @param cell  HSSFCell对象
     * @param value 属性值
     * @return HSSFCell对象
     */
    private static void convertValue(XSSFCell cell, Object value) {
        if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue(DateTimeUtil.formatDateToString((Date) value,null));
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Short) {
            cell.setCellValue((Short) value);
        } else if (value instanceof Byte) {
            cell.setCellValue((Byte) value);
        } else if (value instanceof LocalDateTime) {
            ZoneId zoneId = ZoneId.systemDefault();
            LocalDateTime localDateTime = (LocalDateTime) value;
            ZonedDateTime zdt = localDateTime.atZone(zoneId);
            Date date = Date.from(zdt.toInstant());
            cell.setCellValue(DateTimeUtil.formatDateToString(date,null));
        }else if (value instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) value).doubleValue());
        }

    }

    /**
     * 样式设置
     *
     * @param style
     */
    private static void setStyle(XSSFCellStyle style) {
        // 设置样式
        // 前景色
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);
    }

    /**
     * 生成黑体字
     *
     * @param wb
     * @return
     */
    private static XSSFFont getBlackFont(XSSFWorkbook wb) {
        // 生成一个字体
        XSSFFont font = wb.createFont();
        font.setColor(IndexedColors.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        return font;
    }

    /**
     * 生成加粗黑体字
     *
     * @param wb
     * @return
     */
    private static XSSFFont getBoldFont(XSSFWorkbook wb) {
        // 生成一个字体
        XSSFFont font = wb.createFont();
        font.setColor(IndexedColors.BLACK.index);
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        return font;
    }

}
